package cn.analysis.project.mapper;

/** 全息健康度 */

import cn.analysis.project.domain.CivilCode;
import cn.analysis.project.domain.SysHolographic;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/** 全息健康度mapper */
@Repository
public interface SysHolographicMapper extends BaseMapper<SysHolographic> {

    /** 查询当天的实时健康度 */
    @Select(value = "select code, name, diagtime, health_rating health from holographic h " +
            "where diagtime=(select max(diagtime) from holographic where h.code = code) " +
            "and date_format(diagtime, '%Y-%m-%d')=date_format(CURDATE(), '%Y-%m-%d') and code=#{code} group by code order by diagtime desc")
    List<Map<String, Object>> selectListRealTime(String code);

    /** 按日统计分类 */
    @Select(value = "select date_format(diagtime, '%H:%i:%s') diagtime, health_rating*100 health from holographic " +
            "where DATE_FORMAT(DIAGTIME, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d') " +
            "and code = #{code} order by diagtime asc")
    List<Map<String, Object>> selectListDay(String code);

    /** 按周统计分类 */
    @Select(value = "SELECT (DAY(diagtime)+WEEKDAY(diagtime-INTERVAL DAY(diagtime) DAY)) DIV 7 + 1 diagtime, avg(health_rating)*100 health from holographic " +
            "where DATE_FORMAT(diagtime, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m') " +
            "and code = #{code} group by weeks order by weeks asc;")
    List<Map<String, Object>> selectListWeek(String code);

    /** 按月统计分类 */
    @Select(value = "select avg(health_rating)*100 health, DATE_FORMAT(diagtime,'%m') diagtime from holographic " +
            "where date_format(DIAGTIME, '%Y') = date_format(CURDATE(), '%Y') " +
            "and code = #{code} GROUP BY month order by month asc")
    List<Map<String, Object>> selectListMonth(String code);

    /** 按季度统计分类 */
    @Select(value = "select avg(health_rating)*100 health, (select QUARTER(diagtime)) diagtime from `holographic` " +
            "where DATE_FORMAT(diagtime,'%Y') = DATE_FORMAT(CURDATE(),'%Y') " +
            "and code = #{code} GROUP BY quarters order by quarters asc")
    List<Map<String, Object>> selectListQuarter(String code);

    /** 按年分类统计 */
    @Select(value = "select avg(health_rating)*100 health, DATE_FORMAT(diagtime,'%Y') diagtime from holographic " +
            "where code = #{code} GROUP BY year order by year asc")
    List<Map<String, Object>> selectListYear(String code);

    /** 查询下级数据 */
    @Select(value = "<script>" +
            "select h.diagtime, h.code, h.HEALTH_RATING*100 health from holographic h inner join (select max(diagtime) diagtime, code from holographic group by code) hp " +
            "on h.code=hp.code and h.diagtime=hp.diagtime " +
            "and date_format(h.diagtime, '%Y-%m-%d')=date_format(NOW(), '%Y-%m-%d') and h.code in" +
            "<foreach item='code' index='index' collection='codes' open='(' separator=',' close=')'>" +
            "#{code}" +
            "</foreach>" +
            "</script>"
    )
    List<Map<String, Object>> selectListByCiviCodes(@Param("codes") List<String> codes);
}
